/**
 * 数据库初始化和操作
 */

// 检查数据库连接
function checkDatabaseConnection(env) {
  if (!env.DB) {
    console.error('数据库未正确绑定，请检查 Cloudflare Workers 环境配置');
    throw new Error('数据库未正确绑定，请检查 Cloudflare Workers 环境配置');
  }
  return env.DB;
}

// 初始化数据库表
export async function initDatabase(env) {
  try {
    // 连接到 D1 数据库并检查连接
    const db = checkDatabaseConnection(env);
    
    // 创建 admin_users 表（用于后台登录）
    await db.exec(`
      CREATE TABLE IF NOT EXISTS admin_users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
    `);
    
    // 创建 submissions 表（投稿信息）
    await db.exec(`
      CREATE TABLE IF NOT EXISTS submissions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        grade INTEGER NOT NULL,
        class INTEGER NOT NULL,
        name TEXT NOT NULL,
        title TEXT NOT NULL,
        category INTEGER NOT NULL,
        content TEXT NOT NULL,
        status INTEGER DEFAULT 0, -- 0: 待审核, 1: 已通过, 2: 已拒绝
        is_deleted INTEGER DEFAULT 0, -- 0: 未删除, 1: 已删除
        submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        deleted_at TIMESTAMP NULL
      );
    `);
    
    // 创建默认管理员账号（如果不存在）
    const existingAdmin = await db.prepare(
      'SELECT * FROM admin_users WHERE username = ?'
    ).bind('jxhjschool').first();
    
    if (!existingAdmin) {
      // 这里使用简单的密码哈希示例，实际项目中应使用更强的哈希算法
      await db.prepare(
        'INSERT INTO admin_users (username, password_hash) VALUES (?, ?)'
      ).bind('jxhjschool', 'jxhjschool_2038').run();
      console.log('默认管理员账号创建成功');
    }
    
    console.log('数据库表创建成功');
  } catch (error) {
    console.error('数据库初始化失败:', error);
    throw new Error(`数据库初始化失败: ${error.message}`);
  }
}

// 数据库操作封装类
export class Database {
  constructor(env) {
    try {
      this.db = checkDatabaseConnection(env);
    } catch (error) {
      console.error('数据库连接失败:', error);
      // 创建一个模拟数据库对象，提供基本的错误处理
      this.db = {
        exec: async () => {
          throw new Error('数据库未正确绑定，请检查 Cloudflare Workers 环境配置');
        },
        prepare: () => ({
          bind: () => ({
            first: async () => {
              throw new Error('数据库未正确绑定，请检查 Cloudflare Workers 环境配置');
            },
            all: async () => {
              throw new Error('数据库未正确绑定，请检查 Cloudflare Workers 环境配置');
            },
            run: async () => {
              throw new Error('数据库未正确绑定，请检查 Cloudflare Workers 环境配置');
            }
          })
        })
      };
    }
  }
  
  // 添加新投稿
  async addSubmission(data) {
    try {
      const result = await this.db.prepare(
        `INSERT INTO submissions 
         (grade, class, name, title, category, content)
         VALUES (?, ?, ?, ?, ?, ?)`
      ).bind(
        data.grade,
        data.class,
        data.name,
        data.title,
        data.category,
        data.content
      ).run();
      
      return result.lastInsertRowid;
    } catch (error) {
      console.error('添加投稿失败:', error);
      throw error;
    }
  }
  
  // 获取投稿列表
  async getSubmissions(page = 1, limit = 20, status = null) {
    try {
      const offset = (page - 1) * limit;
      let query = `
        SELECT * FROM submissions 
        WHERE is_deleted = 0
      `;
      
      const params = [];
      
      if (status !== null) {
        query += ` AND status = ?`;
        params.push(status);
      }
      
      query += ` ORDER BY submitted_at DESC LIMIT ? OFFSET ?`;
      params.push(limit, offset);
      
      const submissions = await this.db.prepare(query)
        .bind(...params)
        .all();
      
      // 获取总数
      const countQuery = `SELECT COUNT(*) as total FROM submissions WHERE is_deleted = 0${status !== null ? ' AND status = ?' : ''}`;
      const countParams = status !== null ? [status] : [];
      const total = await this.db.prepare(countQuery)
        .bind(...countParams)
        .first();
      
      return {
        submissions: submissions.results || [],
        total: total.total || 0,
        page,
        limit,
        totalPages: Math.ceil((total.total || 0) / limit)
      };
    } catch (error) {
      console.error('获取投稿列表失败:', error);
      throw error;
    }
  }
  
  // 获取单个投稿
  async getSubmissionById(id) {
    try {
      const submission = await this.db.prepare(
        'SELECT * FROM submissions WHERE id = ? AND is_deleted = 0'
      ).bind(id).first();
      
      return submission;
    } catch (error) {
      console.error('获取投稿详情失败:', error);
      throw error;
    }
  }
  
  // 软删除投稿
  async softDeleteSubmission(id) {
    try {
      const result = await this.db.prepare(
        'UPDATE submissions SET is_deleted = 1, deleted_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP WHERE id = ? AND is_deleted = 0'
      ).bind(id).run();
      
      return result.changes > 0;
    } catch (error) {
      console.error('删除投稿失败:', error);
      throw error;
    }
  }
  
  // 获取已删除的投稿列表（可选功能）
  async getDeletedSubmissions(page = 1, limit = 20) {
    try {
      const offset = (page - 1) * limit;
      
      const submissions = await this.db.prepare(
        'SELECT * FROM submissions WHERE is_deleted = 1 ORDER BY deleted_at DESC LIMIT ? OFFSET ?'
      ).bind(limit, offset).all();
      
      // 获取总数
      const total = await this.db.prepare(
        'SELECT COUNT(*) as total FROM submissions WHERE is_deleted = 1'
      ).first();
      
      return {
        submissions: submissions.results || [],
        total: total.total || 0,
        page,
        limit,
        totalPages: Math.ceil((total.total || 0) / limit)
      };
    } catch (error) {
      console.error('获取已删除投稿列表失败:', error);
      throw error;
    }
  }
  
  // 更新投稿状态
  async updateSubmissionStatus(id, status) {
    try {
      const result = await this.db.prepare(
        'UPDATE submissions SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?'
      ).bind(status, id).run();
      
      return result.changes > 0;
    } catch (error) {
      console.error('更新投稿状态失败:', error);
      throw error;
    }
  }
  
  // 管理员登录验证
  async verifyAdmin(username, password) {
    try {
      const admin = await this.db.prepare(
        'SELECT * FROM admin_users WHERE username = ?'
      ).bind(username).first();
      
      if (!admin) {
        return null;
      }
      
      // 简单的密码验证（实际项目中应使用密码哈希验证）
      if (password === admin.password_hash) {
        return admin;
      }
      
      return null;
    } catch (error) {
      console.error('管理员验证失败:', error);
      throw error;
    }
  }
}